package de.juergenschmied.prefsImpl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import org.apache.log4j.Logger;

public class JDBCBackend {

	private static final Logger logger = Logger.getLogger(JDBCBackend.class);

	private static JDBCBackend singleton;

	private static String url;

	/**
	 * @param url the url to set
	 */
	public static void setUrl(String url) {
		JDBCBackend.url = url;
	}

	private Connection prefsConnection;

	public JDBCBackend(String url) throws SQLException {
		prefsConnection = DriverManager.getConnection(url);

	}

	public static JDBCBackend getInstance() throws SQLException {
		if (singleton == null) {
			singleton = new JDBCBackend(url);
		}
		return singleton;
	}

	public String[] getChildren(int myPK) throws SQLException {
		logger.debug("getChildren(int) - myPK = " + myPK);

		ArrayList<String> arrayList = new ArrayList<String>();
		PreparedStatement stmt = prefsConnection.prepareStatement("select name from nodes where parent = ?");
		try {
			stmt.setInt(1, myPK);
			ResultSet rs = stmt.executeQuery();
			try {
				while (rs.next()) {
					arrayList.add(rs.getString(1));
				}
			} finally {
				rs.close();
			}
		} finally {
			stmt.close();
		}

		return arrayList.toArray(new String[arrayList.size()]);
	}

	public int getChildNodePK(int parentPK, String name) throws SQLException {
		logger.debug("getChildNodePK(int, String) - parentPK=" + parentPK + ", name=" + name);

		PreparedStatement stmt = prefsConnection.prepareStatement("select pk from nodes where parent = ? and name = ?");
		try {
			stmt.setInt(1, parentPK);
			stmt.setString(2, name);
			ResultSet rs = stmt.executeQuery();
			try {
				int myPK = -1;
				if (rs.next()) {
					myPK = rs.getInt(1);
				}

				logger.debug("  getChildNodePK(int, String) - parentPK=" + parentPK + ", name=" + name + ", myPK=" + myPK);
				return myPK;
			} finally {
				rs.close();
			}
		} finally {
			stmt.close();
		}
	}

	public String[] getKeys(int myPK) throws SQLException {
		ArrayList<String> arrayList = new ArrayList<String>();
		PreparedStatement stmt = prefsConnection.prepareStatement("select key from pref_value where parent = ?");
		try {
			stmt.setInt(1, myPK);
			ResultSet rs = stmt.executeQuery();
			try {
				while (rs.next()) {
					arrayList.add(rs.getString(1));
				}
			} finally {
				rs.close();
			}
		} finally {
			stmt.close();
		}

		return arrayList.toArray(new String[arrayList.size()]);
	}

	public String getKey(int myPK, String key) throws SQLException {
		CallableStatement stmt = prefsConnection.prepareCall("begin select value into ? from pref_value where parent = ? and key = ?; end;");
		try {
			stmt.registerOutParameter(1, Types.VARCHAR);
			stmt.setInt(2, myPK);
			stmt.setString(3, key);
			stmt.execute();
			return stmt.getString(1);
		} finally {
			stmt.close();
		}
	}

	public int insertNode(int myPK, String name) throws SQLException {
		logger.debug("insertNode(int, String) - myPK=" + myPK + ", name=" + name);

		int newPK = -1;
		CallableStatement stmt = prefsConnection
				.prepareCall("begin insert into nodes (pk, parent, name) values(s_nodes.nextval, ?, ?) returning pk into ?; end;");
		try {
			stmt.setInt(1, myPK);
			stmt.setString(2, name);
			stmt.registerOutParameter(3, Types.INTEGER);
			stmt.execute();
			newPK = stmt.getInt(3);
		} finally {
			stmt.close();
		}
		return newPK;
	}

	public void removeNode(int myPK) throws SQLException {
		logger.debug("removeNode(int) - myPK=" + myPK);

		PreparedStatement stmt = prefsConnection.prepareStatement("delete from nodes where pk = ?");
		try {
			stmt.setInt(1, myPK);
			stmt.execute();
		} finally {
			stmt.close();
		}
	}

	public void insertValue(int myPK, String key, String value) throws SQLException {
		logger.debug("insertValue(int, String, String) - myPK=" + myPK + ", key=" + key + ", value=" + value);

		PreparedStatement stmt = prefsConnection.prepareStatement("insert into pref_value (pk, parent, key, value) values(s_pref_value.nextval, ?, ?, ?)");
		try {
			stmt.setInt(1, myPK);
			stmt.setString(2, key);
			stmt.setString(3, value);
			stmt.execute();
		} finally {
			stmt.close();
		}
	}

	public void removeValue(int myPK, String key) throws SQLException {
		logger.debug("removeValue(int, String) - myPK=" + myPK + ", key=" + key);

		PreparedStatement stmt = prefsConnection.prepareStatement("delete from pref_value where parent = ? and key = ?");
		try {
			stmt.setInt(1, myPK);
			stmt.setString(2, key);
			stmt.execute();
		} finally {
			stmt.close();
		}
	}

	public void sync() throws SQLException {
		prefsConnection.commit();
	}

	public void flush() throws SQLException {
		prefsConnection.commit();
	}
}
